#region Imported Namespaces
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;

using BO = PHSRAG.Insight21.BusinessObjects;
using DataAccess = PHSRAG.Utility.DataAccess;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using PHSRAG.Insight21.CDS;

using ibex4;

#endregion


namespace PHSRAG.Insight21.COI
{
	/// <summary>
	/// A collection class to hold COI's
	/// </summary>
	public class COICollection : BO.BusinessObject
	{
		#region Constants
		private const string COICollectionKey = "COICollection_574E3124-0394-4277-889C-613D4151ED2B";
		private const bool AscendingSortSequence = false;
		private const string OriginalDataTableName = "original";
		private const string ColumnDateSubmitted = "DateSubmitted";
		#endregion

		#region Instance Variables
		private string currentDataTableName = OriginalDataTableName;
		private bool sortAscending = AscendingSortSequence;
		private string sortColumn = "Id";	// Keep this in sync with what the stored proc orders by.
		#endregion

		#region Constructor
		/// <summary>
		/// Initilize the base class with the specified cache policy.
		/// </summary>
		/// <param name="cachePolicy"></param>
		public COICollection(ICachePolicy cachePolicy)
			: base(cachePolicy)
		{

		}
		#endregion

		#region Public Properties
		/// <summary>
		/// Get the key to be used in all caching operations for this instance of the object.
		/// </summary>
		public static string CacheKey { get { return COICollectionKey; } }

		/// <summary>
		/// Get the total number of COIs.
		/// </summary>
		public int Count
		{
			get
			{
				return (DataContainer.Tables.Count == 0) ? 0 :
						DataContainer.Tables[OriginalDataTableName].Rows.Count;
			}
		}

		/// <summary>
		/// Get the sort sequence.
		/// </summary>
		public bool SortAscending
		{
			get { return sortAscending; }
		}

		/// <summary>
		/// Get the name of the sort column.
		/// </summary>
		public string SortColumn
		{
			get { return sortColumn; }
		}
		#endregion

		#region BusinessObject Methods
		/// <summary>
		/// Cache the current instance using the prevalent cache policy.
		/// </summary>
		public override void Cache()
		{
			CachePolicy.Cache(CacheKey, this);
		}


		/// <summary>
		/// Load the current instance with COI data for the current user.
		/// </summary>
		/// <param name="connectionString">Database connection string</param>
		/// <param name="args">Is expected to contain a single argument - the domain user ID</param>
		public override void Load(string connectionString, params object[] args)
		{
			try
			{
				if (args.Length != 1)
				{
					throw new Exception("COICollection load requires a user ID");
				}

				DataContainer = DataAccess.GetDataSet(connectionString, "GetCOIsForUser",
					DataAccess.BuildSqlParameterArray("@admPersonID", SqlDbType.Int, (int)args[0]));

				if (DataContainer.Tables.Count == 1)
				{
					DataContainer.Tables[0].TableName = OriginalDataTableName;
				}
				else
					throw new Exception("Stored Procedure returned incorrect number of tables in resultset");
			}
			catch (Exception ex)
			{
				throw new Exception("COICollection.Load(): Failed to load COICollection.", ex);
			}
		}


		/// <summary>
		/// Remove the current instance from the cache using the prevalent cache policy.
		/// </summary>
		public override void UnCache()
		{
			CachePolicy.UnCache(CacheKey);
		}

		#endregion

		#region Public methods
		/// <summary>
		/// Adds a new COI to the specified user. The new COI added will be a clone of the
		/// last submitted COI, if any.
		/// 
		/// When a supervisor notifies a person to fill up a COI, the notification date is 
		/// also stored along with the COI object that is created.
		/// </summary>
		/// <param name="cachePolicy">The prevalent cache policy</param>
		/// <param name="admPersonId">Person Id</param>
		/// <param name="dateType">Type of date to be updated.</param>
		public static void AddNewCOI(ICachePolicy cachePolicy, int admPersonId, Constants.COIDateType dateType,Constants.COIType COIType,string NotificationType)
		{
			try
			{
				string reportingPeriod = null;
				int reportingPeriodId = 0;
                bool lastCOIWasTrustee = false;

                DataTable reportingPeriodDetails = DataAccess.GetDataTable(Utility.DataAccess.ConnectionString, "GetCurrentReportingPeriod", 
                                                                            DataAccess.BuildSqlParameterArray("@COITypeId",SqlDbType.Int,(int)COIType));

				if (reportingPeriodDetails != null && reportingPeriodDetails.Rows != null)
				{
					reportingPeriod = Convert.ToString(reportingPeriodDetails.Rows[0]["ReportingPeriod"]);
					reportingPeriodId = Convert.ToInt32(reportingPeriodDetails.Rows[0]["Id"]);
				}

                //RTU COI are created on submission of the Regular Annual COI. As these COI's can be reset to In-Progress status from Submitted.
                //Check to see if an RTU is already created for the current reporting period (+1 for RTU).
                if (COIType == Constants.COIType.RealTimeUpdate)
                {
                    DataTable tableRTUCOI = DataAccess.GetDataTable(Utility.DataAccess.ConnectionString, "GetLastRTUCOI",
                          DataAccess.BuildSqlParameterArray("@admPersonID", SqlDbType.Int, admPersonId));
                    if (tableRTUCOI.Rows.Count > 0 && tableRTUCOI.Rows[0]["ReportingPeriodId"].Equals(reportingPeriodId))
                    {
                        return;
                    }

                }
                //For creating the RTU from Last Submitted COI (which is an Annual COI) use the sp : "GetLastSubmittedCOI" 
                //For creating the Annual COI from Last RTU COI use the sp : "GetLastRTUCOI" 
                DataTable table = DataAccess.GetDataTable(Utility.DataAccess.ConnectionString, COIType == Constants.COIType.RealTimeUpdate ? "GetLastSubmittedCOI" : "GetLastRTUCOI",
                        DataAccess.BuildSqlParameterArray("@admPersonID", SqlDbType.Int, admPersonId));

				ConflictOfInterest newCOI = null;
                ConflictOfInterest lastSubmittedCOI = null;
                COIUser coiUser = new COIUser(cachePolicy);
                coiUser.Load(Utility.DataAccess.ConnectionString, admPersonId,0,0);
                //Has a previous COI
                if (table.Rows.Count > 0)
				{
					lastSubmittedCOI = new ConflictOfInterest(cachePolicy, table.Rows[0]);
					lastSubmittedCOI.LoadQuestionnaireAnswers();

                    foreach (FormsManagement.QuestionnaireAnswer qa in lastSubmittedCOI.QuestionnaireAnswers)
                    {
                        if (qa.QuestionnaireVersion.Questionnaire.Name == "Trustee Disclosure")
                        {
                            lastCOIWasTrustee = true;
                            break;
                        }
                    }

                    // If the trustee status didn't change, clone - otherwise need to create new 
                    // coi
                    if (lastCOIWasTrustee == coiUser.IsOutsideMember)
                    {
                        newCOI = lastSubmittedCOI.Clone();
                        newCOI.LastNotified = DateTime.MinValue;
                        newCOI.DateReviewed = DateTime.MinValue;
                        newCOI.DateSubmitted = DateTime.MinValue;
                    }
                }
              	else//No pervious COI
				{
					newCOI = new ConflictOfInterest(cachePolicy);
					newCOI.AdmPersonId = admPersonId;
                    // add initial questionnaire
                    string trusteeCondition = (coiUser.IsOutsideMember) ? " AND Name = 'Trustee Disclosure'" : " AND Name = 'Individual Disclosure'";

                    DataTable completeQAs = newCOI.GetCompleteQuestionnaireAnswerList();
                    DataRow[] questionnaireAnswerRows = completeQAs.Select(
                        "QuestionnaireCategory = '" +
                        FormsManagement.Constants.COICategory.InitialQuestionnaire + "'" +
                        trusteeCondition);
                    FormsManagement.QuestionnaireVersion qv = new PHSRAG.Insight21.FormsManagement.QuestionnaireVersion(cachePolicy);
                    qv.Load(DataAccess.ConnectionString, questionnaireAnswerRows[0]["QuestionnaireVersionId"]);
                    FormsManagement.QuestionnaireAnswer qa = new PHSRAG.Insight21.FormsManagement.QuestionnaireAnswer(cachePolicy);
                    qa.QuestionnaireVersionId = qv.Id;
                    qa.Name = qv.Questionnaire.Name;
                    newCOI.QuestionnaireAnswers.Add(qa);
				}
				newCOI.ReportingPeriod = reportingPeriod;
				newCOI.ReportingPeriodId = reportingPeriodId;
                if (COIType == Constants.COIType.RealTimeUpdate)
                {
                    newCOI.Status = COI.Constants.COIStatus.InProgress;
                    foreach (FormsManagement.QuestionnaireAnswer qa in lastSubmittedCOI.QuestionnaireAnswers)
                    {
                        qa.Status = Constants.QuestionnaireAnswerStatus.InProgress;
                    }
                }
                else
                {
                    newCOI.Status = COI.Constants.COIStatus.Start;
                }
                newCOI.COIType = (int)COIType;
				newCOI.Save(Utility.DataAccess.GetConnectionString(), dateType);
                newCOI.AuditCOIChanges(COIType == Constants.COIType.AnnualCycle ? Constants.COIActionsForAudit.AnnualCOICreated : Constants.COIActionsForAudit.RTUCOICreated);
                //Setting the status of the Last RTU COI on creation of Annual COI due to OpenEnrollment.
                if (NotificationType == Constants.NotificationTypes.OpenEnrollment && COIType == Constants.COIType.AnnualCycle)
                {
                    if (lastSubmittedCOI != null && lastSubmittedCOI.COIType == (int)Constants.COIType.RealTimeUpdate && lastSubmittedCOI.Status != Constants.COIStatus.Closed)
                    {
                        lastSubmittedCOI.Status = Constants.COIStatus.Closed;
                        lastSubmittedCOI.Save(Utility.DataAccess.GetConnectionString(), Constants.COIDateType.SubmittedDate);
                        lastSubmittedCOI.AuditCOIChanges(Constants.COIActionsForAudit.RTUCOIClosedOnOpenEnrollment);
                    }
                }
			}
			catch (Exception ex)
			{
				throw new Exception("COICollection.AddNewCOI(): Failed to Add New COI.", ex);
			}

		}

		/// <summary>
		/// Get the list of COIs bound by a start and end index (index into the DataContainer loaded
		/// during the load() method). Typically, the start index
		/// is on a "page" boundary, where the "page" referes to a datagrid page.
		/// Note that the total number of records returned may be less than the requested count.
		/// </summary>
		/// <param name="startIndex">Index within the list to begin</param>
		/// <param name="count">Total number of records to return</param>
		/// <param name="sortColumn">Name of the sort column</param>
		/// <param name="sortAscending">Indicates whether or not the sorting should be in Ascending order</param>
		/// <returns>DataTable holding the records requested</returns>
		public DataTable GetPageCOIs(int startIndex, int count, string sortColumn, bool sortAscending)
		{
			try
			{
				DataTable dataTable = DataContainer.Tables[currentDataTableName];
				DataRow[] dataRows = dataTable.Select(string.Empty, string.Format("{0} {1}", sortColumn, ((sortAscending) ? "ASC" : "DESC")));
				DataTable coiList = dataTable.Clone();
				for (int i = startIndex, end = Math.Min(i + count, dataRows.Length); i < end; ++i)
				{
					coiList.LoadDataRow(dataRows[i].ItemArray, true);
				}
				return coiList;
			}
			catch (Exception ex)
			{
				throw new Exception("COICollection.GetPageCOIs(): Failed to get the COIs for the page", ex);
			}
		}

		/// <summary>
		/// Checks if there are any COIs with the specified status in the collection
		/// </summary>
		/// <returns></returns>
		public bool DoesCOIExist(string statusToChange,Constants.COIType COIType)
		{
            return (DataContainer.Tables[currentDataTableName].Select("Status = '" + statusToChange + "' AND COITypeId = " + (int)COIType).Length > 0);
		}

		/// <summary>
		/// Returns Start or In progress COI 
		/// </summary>
		/// <returns>Start or In progress COI</returns>
		public ConflictOfInterest GetStartOrInProgressCOI()
		{
			DataRow[] drArray = DataContainer.Tables[currentDataTableName].Select("Status = '" + Constants.COIStatus.Start + "'");
			ConflictOfInterest coi = null;
			if (drArray.Length > 0)
			{
				coi = new ConflictOfInterest(CachePolicy, drArray[0]);
			}
			else
			{
				drArray = DataContainer.Tables[currentDataTableName].Select("Status = '" + Constants.COIStatus.InProgress + "'");
				if (drArray.Length > 0)
				{
					coi = new ConflictOfInterest(CachePolicy, drArray[0]);
				}
			}
			return coi;
		}

		/// <summary>
		/// Returns Last Submitted COI 
		/// </summary>
		/// <returns>Last Sumitted COI</returns>
		public ConflictOfInterest GetLastSubmittedCOI()
		{
			DataRow[] drArray = DataContainer.Tables[currentDataTableName].Select("Status = '" + Constants.COIStatus.Submitted + "'", ColumnDateSubmitted + " DESC");
			if (drArray.Length > 0)
			{
				ConflictOfInterest coi = new ConflictOfInterest(CachePolicy, drArray[0]);
				return coi;
			}
			return null;

		}
		#endregion

		#region Static methods
		/// <summary>
		/// Terminates the latest Start/InProgress COI
		/// </summary>
		/// <param name="connectionString">Info to connect to the database</param>
		/// <param name="admPersonId">AdmPersonId</param>
		public static void TerminateLatestCOI(string connectionString, int admPersonId)
		{
			try
			{
				Utility.DataAccess.ExecuteNonQuery(connectionString, "TerminateLatestCOI",
						Utility.DataAccess.BuildSqlParameterArray("@admPersonId", SqlDbType.Int, admPersonId));
			}
			catch (Exception ex)
			{
				throw new Exception("Failed to terminate the Start/InProgress COI.", ex);
			}
		}

		

        ///// <summary>
        ///// Returns the summary report for the specified COI Ids
        ///// </summary>
        ///// <returns>A Document object</returns>
        public static Document GetSummaryReport(ICachePolicy cachePolicy, string connectionString, string appPath, DataSet admPersonIdsForXML, string supervisorName, bool filterByLatestReportingPeriod)
        {
            MemoryStream xmlStream = null;
            FileStream xslStream = null;
            MemoryStream pdfStream = null;
            try
            {
                FODocument foDocument = new FODocument();

                string xslFilePath = Path.Combine(appPath, Utility.Settings.GetKeyValue("COISummaryXSLFOFileName", "COISummaryReport.xsl"));
                ibex4.licensing.Generator.LicenseFileLocation = Path.Combine(appPath, @"..\Research\" + Humans.Constants.Ibex.LicenseFileName);

                string logoPath = Path.Combine(appPath, "..");
                logoPath = Path.Combine(logoPath, Utility.Settings.GetKeyValue("PartnersLogo", ""));

                DataTable coiDetails = Utility.DataAccess.GetDataTable(connectionString, "GetCOISummaryXML",
                    Utility.DataAccess.BuildSqlParameterArray("@supervisorName", SqlDbType.VarChar, supervisorName,
                    "@xmlListOfAdmPersonIds", SqlDbType.Text, admPersonIdsForXML.GetXml(),
                    "@filterByLatestReportingPeriod", SqlDbType.Bit, filterByLatestReportingPeriod));

                DataRowCollection rows = coiDetails.Rows;
                string coiXML = string.Empty;
                if (rows.Count > 0)
                    coiXML = rows[0][0].ToString();

                xmlStream = new MemoryStream(Encoding.UTF8.GetBytes(coiXML));
                xslStream = new FileStream(xslFilePath, FileMode.Open, FileAccess.Read);
                pdfStream = new MemoryStream();
                try
                {
                    using (xslStream)
                    {
                        foDocument.generate(xmlStream, xslStream, pdfStream, false);
                    }
                }
                catch (Exception)
                {
                    string bvh = string.Empty;
                }

                Document doc = new Document(cachePolicy);
                doc.Name = "SummaryReport.pdf";

                doc.Blob = pdfStream.ToArray();
                return doc;
            }
            catch (Exception e)
            {
                throw new Exception("Failed to generate the summary report.", e);
            }
            finally
            {
                if (xmlStream != null)
                    xmlStream.Close();
                if (xslStream != null)
                    xslStream.Close();
                if (pdfStream != null)
                    pdfStream.Close();
            }
        }

		public static DataTable GetReportingPeriods(ICachePolicy cachePolicy, string connectionString, bool insertBlankRow)
		{
			try
			{
				DataTable dt = Utility.DataAccess.GetDataTable(connectionString, "GetReportingPeriods", null);

				if (insertBlankRow && dt.Rows.Count > 0)
				{
					DataRow dr = dt.NewRow();
					dr[0] = 0;
					dr[1] = "-- Select --";
					dt.Rows.InsertAt(dr, 0);
				}

				return dt;
			}
			catch (Exception ex)
			{
				throw new Exception("Failed to retrieve reporting periods.", ex);
			}
		}

		public static int AddReportingPeriod(string connectionString, string reportingPeriod)
		{
			try
			{
				return Convert.ToInt32(Utility.DataAccess.ExecuteScalar(connectionString, "SaveReportingPeriod",
					Utility.DataAccess.BuildSqlParameterArray("@reportingPeriod", SqlDbType.VarChar, reportingPeriod)));
			}
			catch (Exception ex)
			{
				throw new Exception("Failed to add reporting period.", ex);
			}
		}
		#endregion

	}
}
